package com.example.cms.DB;


import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;

public class databasecreator {

    /**
     * 初始化数据库和所有表结构
     */
    public static void initializeDatabase() {
        createTables();
        insertTestData();
        System.out.println("数据库初始化完成！");
    }

    /**
     * 创建数据库
     */

    /**
     * 创建所有数据表
     */
    private static void createTables() {
        createDepartmentsTable();
        createDoctorsTable();
        createPatientsTable();
        createSchedulesTable();
        createAppointmentsTable();
        createPaymentsTable();
        createQueuesTable();
        createSystemConfigTable();
    }

    /**
     * 创建科室表
     */
    private static void createDepartmentsTable() {
        String sql = "CREATE TABLE IF NOT EXISTS departments (" +
                "department_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "dept_code VARCHAR(10) UNIQUE NOT NULL, " +
                "dept_name VARCHAR(50) NOT NULL, " +
                "description TEXT, " +
                "location VARCHAR(100), " +
                "phone VARCHAR(20), " +
                "parent_id INT, " +
                "dept_type VARCHAR(20), " +
                "status VARCHAR(10) DEFAULT 'ACTIVE', " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "FOREIGN KEY (parent_id) REFERENCES departments(department_id) ON DELETE SET NULL" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "科室表 departments");
    }

    /**
     * 创建医生表
     */
    private static void createDoctorsTable() {
        String sql = "CREATE TABLE IF NOT EXISTS doctors (" +
                "doctor_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "doctor_no VARCHAR(20) UNIQUE NOT NULL, " +
                "name VARCHAR(50) NOT NULL, " +
                "department_id INT NOT NULL, " +
                "title VARCHAR(20), " +
                "specialty VARCHAR(100), " +
                "introduction TEXT, " +
                "work_years INT, " +
                "education VARCHAR(50), " +
                "status VARCHAR(10) DEFAULT 'ACTIVE', " +
                "office_phone VARCHAR(20), " +
                "email VARCHAR(100), " +
                "consultation_fee DECIMAL(10,2) DEFAULT 0.00, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "医生表 doctors");
    }

    /**
     * 创建患者表
     */
    private static void createPatientsTable() {
        String sql = "CREATE TABLE IF NOT EXISTS patients (" +
                "patient_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "medical_record_no VARCHAR(20) UNIQUE NOT NULL, " +
                "name VARCHAR(50) NOT NULL, " +
                "id_card VARCHAR(18) UNIQUE NOT NULL, " +
                "gender ENUM('M', 'F') NOT NULL, " +
                "birth_date DATE NOT NULL, " +
                "phone VARCHAR(20), " +
                "address VARCHAR(200), " +
                "insurance_type VARCHAR(20), " +
                "allergy_history TEXT, " +
                "emergency_contact VARCHAR(50), " +
                "emergency_phone VARCHAR(20), " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "患者表 patients");
    }

    /**
     * 创建排班表
     */
    private static void createSchedulesTable() {
        String sql = "CREATE TABLE IF NOT EXISTS schedules (" +
                "schedule_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "doctor_id INT NOT NULL, " +
                "schedule_date DATE NOT NULL, " +
                "time_slot ENUM('MORNING', 'AFTERNOON', 'EVENING') NOT NULL, " +
                "total_slots INT DEFAULT 20, " +
                "booked_slots INT DEFAULT 0, " +
                "available_slots INT GENERATED ALWAYS AS (total_slots - booked_slots) STORED, " +
                "status ENUM('AVAILABLE', 'FULL', 'CANCELLED') DEFAULT 'AVAILABLE', " +
                "room_number VARCHAR(20), " +
                "fee DECIMAL(10,2) DEFAULT 0.00, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE, " +
                "UNIQUE KEY unique_doctor_schedule (doctor_id, schedule_date, time_slot)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "排班表 schedules");
    }

    /**
     * 创建预约表
     */
    private static void createAppointmentsTable() {
        String sql = "CREATE TABLE IF NOT EXISTS appointments (" +
                "appointment_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "patient_id INT NOT NULL, " +
                "schedule_id INT NOT NULL, " +
                "appointment_no VARCHAR(30) UNIQUE NOT NULL, " +
                "appointment_time TIMESTAMP NOT NULL, " +
                "queue_number INT, " +
                "status ENUM('PENDING', 'CONFIRMED', 'COMPLETED', 'CANCELLED', 'NOSHOW') DEFAULT 'PENDING', " +
                "fee_amount DECIMAL(10,2) DEFAULT 0.00, " +
                "paid_amount DECIMAL(10,2) DEFAULT 0.00, " +
                "payment_status ENUM('UNPAID', 'PAID', 'REFUNDED') DEFAULT 'UNPAID', " +
                "symptoms TEXT, " +
                "notes TEXT, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE, " +
                "FOREIGN KEY (schedule_id) REFERENCES schedules(schedule_id) ON DELETE CASCADE, " +
                "UNIQUE KEY unique_patient_schedule (patient_id, schedule_id)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "预约表 appointments");
    }

    /**
     * 创建支付记录表
     */
    private static void createPaymentsTable() {
        String sql = "CREATE TABLE IF NOT EXISTS payments (" +
                "payment_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "appointment_id INT NOT NULL, " +
                "payment_no VARCHAR(30) UNIQUE NOT NULL, " +
                "amount DECIMAL(10,2) NOT NULL, " +
                "payment_method ENUM('CASH', 'CARD', 'ALIPAY', 'WECHAT', 'INSURANCE') NOT NULL, " +
                "payment_status ENUM('PENDING', 'SUCCESS', 'FAILED', 'REFUNDED') DEFAULT 'PENDING', " +
                "transaction_no VARCHAR(50), " +
                "payment_time TIMESTAMP NULL, " +
                "payer_name VARCHAR(50), " +
                "payer_id_card VARCHAR(18), " +
                "remarks TEXT, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id) ON DELETE CASCADE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "支付记录表 payments");
    }

    /**
     * 创建就诊队列表
     */
    private static void createQueuesTable() {
        String sql = "CREATE TABLE IF NOT EXISTS queues (" +
                "queue_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "appointment_id INT NOT NULL, " +
                "doctor_id INT NOT NULL, " +
                "queue_number INT NOT NULL, " +
                "schedule_date DATE NOT NULL,"+
                "queue_status ENUM('WAITING', 'CALLED', 'IN_PROGRESS', 'FINISHED', 'CANCELLED') DEFAULT 'WAITING', " +
                "enter_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "call_time TIMESTAMP NULL, " +
                "finish_time TIMESTAMP NULL, " +
                "waiting_time INT DEFAULT 0, " +
                "room_number VARCHAR(20), " +
                "notes TEXT, " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, " +
                "FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id) ON DELETE CASCADE, " +
                "FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE, " +
                "UNIQUE KEY unique_doctor_queue (doctor_id, queue_number, schedule_date)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "就诊队列表 queues");
    }

    /**
     * 创建系统配置表
     */
    private static void createSystemConfigTable() {
        String sql = "CREATE TABLE IF NOT EXISTS system_config (" +
                "config_id INT PRIMARY KEY AUTO_INCREMENT, " +
                "config_key VARCHAR(50) UNIQUE NOT NULL, " +
                "config_value VARCHAR(200), " +
                "description VARCHAR(100), " +
                "config_type VARCHAR(20) DEFAULT 'SYSTEM', " +
                "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                "update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        executeSQL(sql, "系统配置表 system_config");
    }

    /**
     * 插入测试数据
     */
    private static void insertTestData() {
        insertDepartmentData();
        insertDoctorData();
        insertPatientData();
        insertScheduleData();
        insertSystemConfigData();
    }

    /**
     * 插入科室测试数据
     */
    private static void insertDepartmentData() {
        String sql = "INSERT IGNORE INTO departments (dept_code, dept_name, description, location, phone) VALUES " +
                "('NEIKE', '内科', '治疗内科疾病，包括心血管、呼吸、消化等系统疾病', '门诊楼2层', '010-12345671'), " +
                "('WAIKE', '外科', '手术治疗，包括普外科、骨科、神经外科等', '门诊楼3层', '010-12345672'), " +
                "('ERKE', '儿科', '儿童疾病预防、诊断和治疗', '门诊楼1层', '010-12345673'), " +
                "('FUCHAN', '妇产科', '妇科和产科疾病诊治', '门诊楼4层', '010-12345674'), " +
                "('GUKE', '骨科', '骨骼、关节疾病治疗', '门诊楼3层', '010-12345675'), " +
                "('SHENJING', '神经科', '神经系统疾病诊治', '门诊楼2层', '010-12345676')";

        executeSQL(sql, "科室测试数据");
    }

    /**
     * 插入医生测试数据
     */
    private static void insertDoctorData() {
        String sql = "INSERT IGNORE INTO doctors (doctor_no, name, department_id, title, specialty, consultation_fee, office_phone) VALUES " +
                "('D001', '张明医生', 1, '主任医师', '心血管疾病、高血压', 50.00, '010-12340001'), " +
                "('D002', '李华医生', 1, '副主任医师', '呼吸系统疾病', 40.00, '010-12340002'), " +
                "('D003', '王强医生', 2, '主任医师', '普外科手术', 60.00, '010-12340003'), " +
                "('D004', '赵雪医生', 3, '主治医师', '儿科常见病', 30.00, '010-12340004'), " +
                "('D005', '刘芳医生', 4, '副主任医师', '妇科疾病', 45.00, '010-12340005'), " +
                "('D006', '陈伟医生', 5, '主任医师', '骨科手术', 55.00, '010-12340006')";

        executeSQL(sql, "医生测试数据");
    }

    /**
     * 插入患者测试数据
     */
    private static void insertPatientData() {
        String sql = "INSERT IGNORE INTO patients (medical_record_no, name, id_card, gender, birth_date, phone, insurance_type) VALUES " +
                "('MR20240001', '张三', '110101199001011234', 'M', '1990-01-01', '13800138001', '城镇职工医保'), " +
                "('MR20240002', '李四', '110101199002021235', 'F', '1990-02-02', '13800138002', '城乡居民医保'), " +
                "('MR20240003', '王五', '110101199003031236', 'M', '1990-03-03', '13800138003', '商业保险'), " +
                "('MR20240004', '赵六', '110101199004041237', 'F', '1990-04-04', '13800138004', '自费'), " +
                "('MR20240005', '钱七', '110101199005051238', 'M', '1990-05-05', '13800138005', '城镇职工医保')";

        executeSQL(sql, "患者测试数据");
    }

    /**
     * 插入排班测试数据
     */
    private static void insertScheduleData() {
        // 插入未来7天的排班数据
        String sql = "INSERT IGNORE INTO schedules (doctor_id, schedule_date, time_slot, total_slots, room_number, fee) VALUES " +
                "(1, CURDATE(), 'MORNING', 15, '201', 50.00), " +
                "(1, CURDATE(), 'AFTERNOON', 10, '201', 50.00), " +
                "(2, CURDATE(), 'MORNING', 20, '202', 40.00), " +
                "(3, CURDATE() + INTERVAL 1 DAY, 'MORNING', 15, '301', 60.00), " +
                "(4, CURDATE() + INTERVAL 1 DAY, 'AFTERNOON', 12, '101', 30.00), " +
                "(5, CURDATE() + INTERVAL 2 DAY, 'MORNING', 18, '401', 45.00), " +
                "(6, CURDATE() + INTERVAL 2 DAY, 'AFTERNOON', 15, '302', 55.00)";

        executeSQL(sql, "排班测试数据");
    }

    /**
     * 插入系统配置数据
     */
    private static void insertSystemConfigData() {
        String sql = "INSERT IGNORE INTO system_config (config_key, config_value, description) VALUES " +
                "('hospital_name', '北京协和医院', '医院名称'), " +
                "('max_appointments_per_patient', '3', '每个患者最大预约数'), " +
                "('cancel_before_hours', '24', '取消预约提前小时数'), " +
                "('work_start_time', '08:00', '上班时间'), " +
                "('work_end_time', '17:00', '下班时间'), " +
                "('morning_slot_end', '12:00', '上午时段结束时间'), " +
                "('afternoon_slot_start', '13:00', '下午时段开始时间'), " +
                "('default_slots_per_doctor', '20', '医生默认号源数')";

        executeSQL(sql, "系统配置数据");
    }

    /**
     * 执行SQL语句的通用方法
     */
    private static void executeSQL(String sql, String tableName) {
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println(tableName + " 创建/初始化成功！");
        } catch (SQLException e) {
            System.err.println(tableName + " 创建/初始化失败: " + e.getMessage());
        }
    }

    /**
     * 删除所有表（重置数据库）
     */
    public static void dropAllTables() {
        String[] tables = {
                "payments", "queues", "appointments", "schedules",
                "patients", "doctors", "departments", "system_config"
        };

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement()) {

            // 禁用外键约束检查
            stmt.execute("SET FOREIGN_KEY_CHECKS = 0");

            for (String table : tables) {
                try {
                    stmt.executeUpdate("DROP TABLE IF EXISTS " + table);
                    System.out.println("表 " + table + " 删除成功！");
                } catch (SQLException e) {
                    System.err.println("删除表 " + table + " 失败: " + e.getMessage());
                }
            }

            // 重新启用外键约束检查
            stmt.execute("SET FOREIGN_KEY_CHECKS = 1");

        } catch (SQLException e) {
            System.err.println("删除表时发生错误: " + e.getMessage());
        }
    }

    /**
     * 检查数据库连接
     */
    public static boolean checkDatabaseConnection() {
        try (Connection conn = DBUtil.getConnection()) {
            return conn != null && !conn.isClosed();
        } catch (SQLException e) {
            System.err.println("数据库连接检查失败: " + e.getMessage());
            return false;
        }
    }
}